package dao;

import java.util.Vector;
import java.sql.ResultSet;
import java.sql.Statement;
import java.sql.SQLException;

class DAOBase {
        private final Object primaryKey;

        public DAOBase(Object primaryKey) {
                this.primaryKey = primaryKey;
        }

        /* Query a field for this object in another table.
         * 'table' specifies the name of the table to use.
         * 'field' is the column representing the field.
         * 'foreignKey' will be matches against this.primaryKey in the table.
         * Returns a vector of strings with the values of the columns
         * corresponding to the query.
         */
        protected Vector<String> getExternalField(String table,
                                                  String field,
                                                  String foreignKey)
        throws SQLException
        {
                String SQLQuery;
                ResultSet result;
                Statement statement;
                Vector<String> food = new Vector<String>();

                SQLQuery = String.format("SELECT %s " +
                                         "FROM %s, Bird " +
                                         "WHERE %s.%s = " +
                                         quoteForSQL(primaryKey),
                                         field, table, table, foreignKey);

                statement = ConnectionManager.getStatement();
                result = statement.executeQuery(SQLQuery);

                while (result.next()) {
                        food.add(result.getString(field));
                }

                return food;
        }

        /* Add an external field to an object.
         * 'table' specifies the table in which to keep the field.
         * 'values' specifies the values to insert.
         */
        protected void addExternalField(String table, Object[] values)
        throws SQLException
        {
                int i;
                String SQLInsert;

                SQLInsert = "INSERT INTO " + table + " VALUES (";
                for (i = 0; i < values.length-1; i++) {
                        SQLInsert += quoteForSQL(values[i]) + ", ";
                }

                SQLInsert += quoteForSQL(values[i]) + ")";

                ConnectionManager.getStatement().executeUpdate(SQLInsert);
        }

        /* Remove an external field from a table.
         * 'table' specifies the table from which to remove.
         * 'field' specifies the name of the field.
         * 'foreignKey' is the name of the foreign key in the 'table'.
         * 'value' is the value to match.
         */
        protected void removeExternalField(String table, String field,
                                           String foreignKey, Object value)
        throws SQLException
        {
                String SQLDelete;

                SQLDelete = String.format("DELETE FROM %s WHERE " +
                                          "%s.%s = %s AND " +
                                          "%s.%s = %s",
                                          table, table, foreignKey,
                                          quoteForSQL(primaryKey),
                                          table, field, quoteForSQL(value));

                ConnectionManager.getStatement().executeUpdate(SQLDelete);
        }

        /* Quote a value if necessary to be used in SQL queries. */
        protected static String quoteForSQL(Object value)
        {
                return value.getClass() == String.class ?
                       "'" + value + "'" : "" + value;
        }
}
